package com.erp.framework.util.mainmenu;

import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import com.erp.app.common.ProjectConstants;
import com.erp.framework.util.CommonUtil;
import com.erp.framework.util.logger.Logger;

public class MenuGroupDAO implements com.erp.framework.SimpleConstants {

    public ArrayList getMenuGroupList(String companyCode, String userId, String moduleName, Connection conn) {

        ArrayList menuGroupList = new ArrayList();

        Statement stmt = null;
        ResultSet rs = null;

        try {
			String sysLang = System.getProperty(SESSION_SYSTEM_LANG);

			if(CommonUtil.isEmpty(sysLang)){
				sysLang = LANG_DEFAULT;
			}
			
            String query = "select * from (SELECT A.GROUP_LEVEL, nvl(B.VIEW_LEVEL,0) VIEW_LEVEL, "
                    + " nvl(B.CREATE_LEVEL,0) CREATE_LEVEL, nvl(B.UPDATE_LEVEL,0) UPDATE_LEVEL, "
                    + " nvl(B.DELETE_LEVEL,0) DELETE_LEVEL, C.COMPANY_CODE, C.GROUP_ID, C.MENU_ID, C.PARENT_MENU_ID, "
                    + " NVL(E.MENU_WEB_LABEL, C.MENU_NAME) menu_name, C.MENU_TYPE, C.MENU_ORDER, C.MENU_URL, C.MENU_ICON, D.MENU_IMAGE, C.ROOT_MENU_ID"
                    + " FROM T_USER A, T_COMPANY_MENU_GROUP B, T_MENU_GROUP C, T_MENU_GROUP_IMAGE D, T_MENU_GROUP_LABEL E"
                    + " WHERE A.COMPANY_CODE = B.COMPANY_CODE" 
					+ " AND A.ROLE_GROUP_ID = B.ROLE_GROUP_ID"
                    + " AND B.COMPANY_CODE=C.COMPANY_CODE" 
					+ " AND B.GROUP_ID = C.GROUP_ID" 
					+ " AND B.MENU_ID = C.MENU_ID"
                    + " AND C.COMPANY_CODE=D.COMPANY_CODE(+)" 
					+ " AND C.GROUP_ID = D.GROUP_ID(+)"
                    + " AND C.MENU_ID = D.MENU_ID(+)" 
					+ " AND C.GROUP_ID=E.GROUP_ID(+)" 
					+ " AND C.MENU_ID=E.MENU_ID(+)" 
					+ " AND C.COMPANY_CODE=E.COMPANY_CODE(+)" 
					+ " AND A.COMPANY_CODE='" + companyCode + "'" 
					+ " AND A.USER_CODE='" + userId + "'"
                    + " AND E.LANG_CODE(+) = '" + sysLang + "' "
                    + " AND C.STATUS = '" + GENERIC_ACTIVE + "' "
					+ " AND (C.PARENT_MENU_ID='" + moduleName + "'"
                    + " OR parent_menu_id='" + PARENT_MENU_MENU_TAB + "'"                    
                    + " OR parent_menu_id='" + PARENT_MENU_ENQ_TAB + "'"
                    + " )"
                    + " union all "
                    + "SELECT A.GROUP_LEVEL, nvl(B.VIEW_LEVEL,0) VIEW_LEVEL, "
                    + " nvl(B.CREATE_LEVEL,0) CREATE_LEVEL, nvl(B.UPDATE_LEVEL,0) UPDATE_LEVEL, "
                    + " nvl(B.DELETE_LEVEL,0) DELETE_LEVEL, C.COMPANY_CODE, C.GROUP_ID, C.MENU_ID, C.PARENT_MENU_ID, "
                    + " NVL(E.MENU_WEB_LABEL, C.MENU_NAME) menu_name, C.MENU_TYPE, C.MENU_ORDER, C.MENU_URL, C.MENU_ICON, D.MENU_IMAGE, C.ROOT_MENU_ID"
                    + " FROM T_USER A, T_COMPANY_MENU_GROUP B, T_MENU_GROUP2 C, T_MENU_GROUP_IMAGE D, T_MENU_GROUP_LABEL E"
                    + " WHERE A.COMPANY_CODE = B.COMPANY_CODE" 
					+ " AND A.ROLE_GROUP_ID = B.ROLE_GROUP_ID"
                    + " AND B.COMPANY_CODE=C.COMPANY_CODE" 
					+ " AND B.GROUP_ID = C.GROUP_ID" 
					+ " AND B.MENU_ID = C.MENU_ID"
                    + " AND C.COMPANY_CODE=D.COMPANY_CODE(+)" 
					+ " AND C.GROUP_ID = D.GROUP_ID(+)"
                    + " AND C.MENU_ID = D.MENU_ID(+)" 
					+ " AND C.GROUP_ID=E.GROUP_ID(+)" 
					+ " AND C.MENU_ID=E.MENU_ID(+)" 
					+ " AND C.COMPANY_CODE=E.COMPANY_CODE(+)" 
					+ " AND A.COMPANY_CODE='" + companyCode + "'" 
					+ " AND A.USER_CODE='" + userId + "'"
                    + " AND E.LANG_CODE(+) = '" + sysLang + "' "
                    + " AND C.STATUS = '" + GENERIC_ACTIVE + "' "
					+ " AND (C.PARENT_MENU_ID='" + moduleName + "'"
                    + " OR parent_menu_id='" + PARENT_MENU_MENU_TAB + "'"                    
                    + " OR parent_menu_id='" + PARENT_MENU_ENQ_TAB + "'"
                    + " ))"
                    + " ORDER BY PARENT_MENU_ID, MENU_ORDER";
            
            Logger.sql(this, "getMenuGroupList(), sql=" + query);
            Logger.field(this, "getMenuGroupList(), companyCode=[" + companyCode + "]");
            Logger.field(this, "getMenuGroupList(), userId=[" + userId + "]");
            Logger.field(this, "getMenuGroupList(), moduleName=[" + moduleName + "]");

            stmt = conn.createStatement();
            rs = stmt.executeQuery(query);
            if (rs != null) {
                while (rs.next()) {
                    MenuGroup menuGroup = (MenuGroup) getResultSetValues(rs);
                    menuGroupList.add(menuGroup);
                }
            } else {
                Logger.warn(this, "getMenuGroupList(), rs is null");
            }
        } catch (Exception e) {
            Logger.error(this, "getMenuGroupList(), error: " + e.getMessage());
            if (Logger.isTraceLevel()) {
                e.printStackTrace();
            }
        } finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (Exception e) {
                Logger.error(this, "getMenuGroupList(), error: " + e.getMessage());
                if (Logger.isTraceLevel()) {
                    e.printStackTrace();
                }
            }
            try {
                if (stmt != null)
                    stmt.close();
            } catch (Exception e) {
                Logger.error(this, "getMenuGroupList(), error: " + e.getMessage());
                if (Logger.isTraceLevel()) {
                    e.printStackTrace();
                }
            }
        }

        return menuGroupList;
    }
    
    public ArrayList getMenuGroupListByModule(String companyCode, String userId, String moduleName, Connection conn) {

        ArrayList menuGroupList = new ArrayList();

        Statement stmt = null;
        ResultSet rs = null;

        try {
			String sysLang = System.getProperty(SESSION_SYSTEM_LANG);

			if(CommonUtil.isEmpty(sysLang)){
				sysLang = LANG_DEFAULT;
			}

            String query = "select * from (SELECT A.GROUP_LEVEL, nvl(B.VIEW_LEVEL,0) VIEW_LEVEL, "
                    + " nvl(B.CREATE_LEVEL,0) CREATE_LEVEL, nvl(B.UPDATE_LEVEL,0) UPDATE_LEVEL, "
                    + " nvl(B.DELETE_LEVEL,0) DELETE_LEVEL, C.COMPANY_CODE, C.GROUP_ID, C.MENU_ID, C.PARENT_MENU_ID, "
                    + " NVL(E.MENU_WEB_LABEL, C.MENU_NAME) menu_name, C.MENU_TYPE, C.MENU_ORDER, C.MENU_URL, C.MENU_ICON, D.MENU_IMAGE, C.ROOT_MENU_ID"
                    + " FROM T_USER A, T_COMPANY_MENU_GROUP B, T_MENU_GROUP C, T_MENU_GROUP_IMAGE D, T_MENU_GROUP_LABEL E"
                    + " WHERE A.COMPANY_CODE = B.COMPANY_CODE" 
					+ " AND A.ROLE_GROUP_ID = B.ROLE_GROUP_ID"
                    + " AND B.COMPANY_CODE=C.COMPANY_CODE" 
					+ " AND B.GROUP_ID = C.GROUP_ID" 
					+ " AND B.MENU_ID = C.MENU_ID"
                    + " AND C.COMPANY_CODE=D.COMPANY_CODE(+)" 
					+ " AND C.GROUP_ID = D.GROUP_ID(+)"
                    + " AND C.MENU_ID = D.MENU_ID(+)" 
					+ " AND C.GROUP_ID=E.GROUP_ID(+)" 
					+ " AND C.MENU_ID=E.MENU_ID(+)" 
					+ " AND C.COMPANY_CODE=E.COMPANY_CODE(+)" 
					+ " AND A.COMPANY_CODE='" + companyCode + "'" 
					+ " AND A.USER_CODE='" + userId + "'" 
                    + " AND E.LANG_CODE(+) = '" + sysLang + "' "
                    + " AND C.STATUS = '" + GENERIC_ACTIVE + "' "
					+ " AND (C.PARENT_MENU_ID='" + moduleName + "'"
                    + " )"
                    + "  union all "
                    +"SELECT A.GROUP_LEVEL, nvl(B.VIEW_LEVEL,0) VIEW_LEVEL, "
                    + " nvl(B.CREATE_LEVEL,0) CREATE_LEVEL, nvl(B.UPDATE_LEVEL,0) UPDATE_LEVEL, "
                    + " nvl(B.DELETE_LEVEL,0) DELETE_LEVEL, C.COMPANY_CODE, C.GROUP_ID, C.MENU_ID, C.PARENT_MENU_ID, "
                    + " NVL(E.MENU_WEB_LABEL, C.MENU_NAME) menu_name, C.MENU_TYPE, C.MENU_ORDER, C.MENU_URL, C.MENU_ICON, D.MENU_IMAGE, C.ROOT_MENU_ID"
                    + " FROM T_USER A, T_COMPANY_MENU_GROUP B, T_MENU_GROUP2 C, T_MENU_GROUP_IMAGE D, T_MENU_GROUP_LABEL E"
                    + " WHERE A.COMPANY_CODE = B.COMPANY_CODE" 
					+ " AND A.ROLE_GROUP_ID = B.ROLE_GROUP_ID"
                    + " AND B.COMPANY_CODE=C.COMPANY_CODE" 
					+ " AND B.GROUP_ID = C.GROUP_ID" 
					+ " AND B.MENU_ID = C.MENU_ID"
                    + " AND C.COMPANY_CODE=D.COMPANY_CODE(+)" 
					+ " AND C.GROUP_ID = D.GROUP_ID(+)"
                    + " AND C.MENU_ID = D.MENU_ID(+)" 
					+ " AND C.GROUP_ID=E.GROUP_ID(+)" 
					+ " AND C.MENU_ID=E.MENU_ID(+)" 
					+ " AND C.COMPANY_CODE=E.COMPANY_CODE(+)" 
					+ " AND A.COMPANY_CODE='" + companyCode + "'" 
					+ " AND A.USER_CODE='" + userId + "'" 
                    + " AND E.LANG_CODE(+) = '" + sysLang + "' "
                    + " AND C.STATUS = '" + GENERIC_ACTIVE + "' "
					+ " AND (C.PARENT_MENU_ID='" + moduleName + "'"
                    + " ) )"
                    + " ORDER BY PARENT_MENU_ID, MENU_ORDER";
            
            Logger.sql(this, "getMenuGroupListByModule(), sql=" + query);
            Logger.field(this, "getMenuGroupListByModule(), companyCode=[" + companyCode + "]");
            Logger.field(this, "getMenuGroupListByModule(), userId=[" + userId + "]");
            Logger.field(this, "getMenuGroupListByModule(), moduleName=[" + moduleName + "]");

            stmt = conn.createStatement();
            rs = stmt.executeQuery(query);
            if (rs != null) {
                while (rs.next()) {
                    MenuGroup menuGroup = (MenuGroup) getResultSetValues(rs);
                    menuGroupList.add(menuGroup);
                }
            } else {
                Logger.warn(this, "getMenuGroupListByModule(), rs is null");
            }
        } catch (Exception e) {
            Logger.error(this, "getMenuGroupListByModule(), error: " + e.getMessage());
            if (Logger.isTraceLevel()) {
                e.printStackTrace();
            }
        } finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (Exception e) {
                Logger.error(this, "getMenuGroupListByModule(), error: " + e.getMessage());
                if (Logger.isTraceLevel()) {
                    e.printStackTrace();
                }
            }
            try {
                if (stmt != null)
                    stmt.close();
            } catch (Exception e) {
                Logger.error(this, "getMenuGroupListByModule(), error: " + e.getMessage());
                if (Logger.isTraceLevel()) {
                    e.printStackTrace();
                }
            }
        }

        return menuGroupList;
    }
    
    public ArrayList getAllMenuGroupList(String companyCode, String userId, Connection conn) {

        ArrayList menuGroupList = new ArrayList();

        Statement stmt = null;
        ResultSet rs = null;

        try {
			String sysLang = System.getProperty(SESSION_SYSTEM_LANG);

			if(CommonUtil.isEmpty(sysLang)){
				sysLang = LANG_DEFAULT;
			}
            String query = "select * from (SELECT A.GROUP_LEVEL, nvl(B.VIEW_LEVEL,0) VIEW_LEVEL, "
                    + " nvl(B.CREATE_LEVEL,0) CREATE_LEVEL, nvl(B.UPDATE_LEVEL,0) UPDATE_LEVEL, "
                    + " nvl(B.DELETE_LEVEL,0) DELETE_LEVEL, C.COMPANY_CODE, C.GROUP_ID, C.MENU_ID, C.PARENT_MENU_ID, "
                    + " NVL(E.MENU_WEB_LABEL, C.MENU_NAME) menu_name, C.MENU_TYPE, C.MENU_ORDER, C.MENU_URL, C.MENU_ICON, D.MENU_IMAGE, C.ROOT_MENU_ID"
                    + " FROM T_USER A, T_COMPANY_MENU_GROUP B, T_MENU_GROUP C, T_MENU_GROUP_IMAGE D, T_MENU_GROUP_LABEL E"
                    + " WHERE A.COMPANY_CODE = B.COMPANY_CODE" 
					+ " AND A.ROLE_GROUP_ID = B.ROLE_GROUP_ID"
                    + " AND B.COMPANY_CODE=C.COMPANY_CODE" 
					+ " AND B.GROUP_ID = C.GROUP_ID" 
					+ " AND B.MENU_ID = C.MENU_ID"
                    + " AND C.COMPANY_CODE=D.COMPANY_CODE(+)" 
					+ " AND C.GROUP_ID = D.GROUP_ID(+)"
                    + " AND C.MENU_ID = D.MENU_ID(+)" 
					+ " AND C.GROUP_ID=E.GROUP_ID(+)" 
					+ " AND C.MENU_ID=E.MENU_ID(+)" 
					+ " AND C.COMPANY_CODE=E.COMPANY_CODE(+)" 
					+ " AND A.COMPANY_CODE='" + companyCode + "'" 
					+ " AND A.USER_CODE='" + userId + "'" 
					+ " AND E.LANG_CODE(+) = '" + sysLang + "' "
					+ " AND C.STATUS = '" + GENERIC_ACTIVE + "' "
					+ " union all "
					+ "SELECT A.GROUP_LEVEL, nvl(B.VIEW_LEVEL,0) VIEW_LEVEL, "
                    + " nvl(B.CREATE_LEVEL,0) CREATE_LEVEL, nvl(B.UPDATE_LEVEL,0) UPDATE_LEVEL, "
                    + " nvl(B.DELETE_LEVEL,0) DELETE_LEVEL, C.COMPANY_CODE, C.GROUP_ID, C.MENU_ID, C.PARENT_MENU_ID, "
                    + " NVL(E.MENU_WEB_LABEL, C.MENU_NAME) menu_name, C.MENU_TYPE, C.MENU_ORDER, C.MENU_URL, C.MENU_ICON, D.MENU_IMAGE, C.ROOT_MENU_ID"
                    + " FROM T_USER A, T_COMPANY_MENU_GROUP B, T_MENU_GROUP2 C, T_MENU_GROUP_IMAGE D, T_MENU_GROUP_LABEL E"
                    + " WHERE A.COMPANY_CODE = B.COMPANY_CODE" 
					+ " AND A.ROLE_GROUP_ID = B.ROLE_GROUP_ID"
                    + " AND B.COMPANY_CODE=C.COMPANY_CODE" 
					+ " AND B.GROUP_ID = C.GROUP_ID" 
					+ " AND B.MENU_ID = C.MENU_ID"
                    + " AND C.COMPANY_CODE=D.COMPANY_CODE(+)" 
					+ " AND C.GROUP_ID = D.GROUP_ID(+)"
                    + " AND C.MENU_ID = D.MENU_ID(+)" 
					+ " AND C.GROUP_ID=E.GROUP_ID(+)" 
					+ " AND C.MENU_ID=E.MENU_ID(+)" 
					+ " AND C.COMPANY_CODE=E.COMPANY_CODE(+)" 
					+ " AND A.COMPANY_CODE='" + companyCode + "'" 
					+ " AND A.USER_CODE='" + userId + "'" 
					+ " AND E.LANG_CODE(+) = '" + sysLang + "' "
					+ " AND C.STATUS = '" + GENERIC_ACTIVE + "' )"
                    + " ORDER BY PARENT_MENU_ID, MENU_ORDER";
            
            Logger.sql(this, "getAllMenuGroupList(), sql=" + query);
            Logger.field(this, "getAllMenuGroupList(), companyCode=[" + companyCode + "]");
            Logger.field(this, "getAllMenuGroupList(), userId=[" + userId + "]");

            stmt = conn.createStatement();
            rs = stmt.executeQuery(query);
            if (rs != null) {
                while (rs.next()) {
                    MenuGroup menuGroup = (MenuGroup) getResultSetValues(rs);
                    menuGroupList.add(menuGroup);
                }
            } else {
                Logger.warn(this, "getAllMenuGroupList(), rs is null");
            }
        } catch (Exception e) {
            Logger.error(this, "getAllMenuGroupList(), error: " + e.getMessage());
            if (Logger.isTraceLevel()) {
                e.printStackTrace();
            }
        } finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (Exception e) {
                Logger.error(this, "getAllMenuGroupList(), error: " + e.getMessage());
                if (Logger.isTraceLevel()) {
                    e.printStackTrace();
                }
            }
            try {
                if (stmt != null)
                    stmt.close();
            } catch (Exception e) {
                Logger.error(this, "getAllMenuGroupList(), error: " + e.getMessage());
                if (Logger.isTraceLevel()) {
                    e.printStackTrace();
                }
            }
        }

        return menuGroupList;
    }
    
    protected Object getResultSetValues(ResultSet rs) throws Exception {

        MenuGroup menuGroup = new MenuGroup();

        menuGroup.setCompanyCode(rs.getString("COMPANY_CODE"));
        menuGroup.setMenuId(rs.getString("MENU_ID"));
        menuGroup.setParentMenuId(rs.getString("PARENT_MENU_ID"));
        menuGroup.setName(rs.getString("MENU_NAME"));
        menuGroup.setGroupId(rs.getString("GROUP_ID"));
        menuGroup.setMenuType(rs.getString("MENU_TYPE"));
        menuGroup.setMenuOrder(rs.getString("MENU_ORDER"));
        menuGroup.setMenuUrl(rs.getString("MENU_URL"));
        menuGroup.setMenuIcon(getBytes(rs, "MENU_ICON"));
        menuGroup.setMenuImage(getBytes(rs, "MENU_IMAGE"));
        menuGroup.setViewLevel(rs.getString("VIEW_LEVEL"));
        menuGroup.setCreateLevel(rs.getString("CREATE_LEVEL"));
        menuGroup.setUpdateLevel(rs.getString("UPDATE_LEVEL"));
        menuGroup.setDeleteLevel(rs.getString("DELETE_LEVEL"));
        menuGroup.setRootMenuId(rs.getString("ROOT_MENU_ID"));

        String groupLevel = CommonUtil.nullToString(rs.getString("GROUP_LEVEL"));
        String viewLevel = CommonUtil.nullToString(rs.getString("VIEW_LEVEL"));
        int intGroupLevel = 0;
        int intViewLevel = 0;

        if (!groupLevel.equals("")) {
            intGroupLevel = CommonUtil.strToInteger(groupLevel).intValue();
        }
        if (!viewLevel.equals("")) {
            intViewLevel = CommonUtil.strToInteger(viewLevel).intValue();
        }

        if (intGroupLevel <= intViewLevel)
            menuGroup.setViewDisplay(ProjectConstants.GENERIC_YES);
        else
            menuGroup.setViewDisplay(ProjectConstants.GENERIC_NO);

        return menuGroup;
    }

    protected byte[] getBytes(ResultSet rs, String colName) throws Exception {

        byte[] output = null;
        InputStream istream = rs.getBinaryStream(colName);
        if (istream != null) {
            ByteArrayOutputStream bout = new ByteArrayOutputStream();
            int bytesRead = 0;
            byte[] tmp = new byte[8096];
            while ((bytesRead = istream.read(tmp)) != -1) {
                bout.write(tmp, 0, bytesRead);
            }
            output = bout.toByteArray();
        }
        return output;
    }
}